#!/usr/bin/python
# vi: ts=4

import sys,getopt
import commands
import calendar
import time
import os
import JoDef
import JoLog

import pdb

def usage():
	print """
	Usage: JoSql [options]
		-h, --help
		-l, --list
		-t, --table
		-m, --map
		-u, --user
		-s, --select       select statement to execute against the journal
	"""

def sql_string(sql,user):
	if user:
		sqlt=  (sql,JoDef.JODBBIN,JoDef.JODBDIR,user,JoDef.JODBFILE)
		cmd = "echo \"%s;\" | %s %s%s/%s " % sqlt
	else:
		sqlt= (sql,JoDef.JODBBIN,JoDef.JODBDIR,JoDef.JODBFILE)
		cmd = "echo \"%s;\" | %s %s%s " % sqlt
	JoLog.setup("jo.sql").info(cmd)
	return commands.getoutput(cmd)
	
def sql_list(sql,user):
	if user:
		sqlt= (sql,JoDef.JODBBIN,JoDef.JODBDIR,user,JoDef.JODBFILE)
		cmd = "echo \"%s;\" | %s %s%s/%s " % sqlt
	else:
		sqlt= (sql,JoDef.JODBBIN,JoDef.JODBDIR,JoDef.JODBFILE)
		cmd = "echo \"%s;\" | %s %s%s " % sqlt
	JoLog.setup("jo.sql").info(cmd)
	return commands.getoutput(cmd).split("\n")
	
def sql_table(sql,user,sumcols=None):
	if user:
		sqlt= (sql,JoDef.JODBBIN,JoDef.JODBDIR,user,JoDef.JODBFILE)
		cmd = "echo \"%s;\" | %s %s%s/%s " % sqlt
	else:
		sqlt= (sql,JoDef.JODBBIN,JoDef.JODBDIR,JoDef.JODBFILE)
		cmd = "echo \"%s;\" | %s %s%s " % sqlt
	select_table = []
	select_list = commands.getoutput(cmd).split("\n")
	sums = []
	if sumcols:
		sums =  [0]*(max(sumcols)+1)
	for row in select_list:
		cols = row.split('|')
		if sumcols:
			for i in sumcols:
				sums[i] += float(cols[i])
		select_table.append(row.split('|'))
	if sumcols:
		for i in range(0,len(sums)):
			sums[i] = "%0.6f"%sums[i]	
		select_table.append(sums)
	JoLog.setup("jo.sql").info(cmd)
	return select_table;

def sql_map(sql,keycol,savecol,nullkey,user):
	if user:
		sqlt= (sql,JoDef.JODBBIN,JoDef.JODBDIR,user,JoDef.JODBFILE)
		cmd = "echo \"%s;\" | %s %s%s/%s " % sqlt
	else:
		sqlt= (sql,JoDef.JODBBIN,JoDef.JODBDIR,JoDef.JODBFILE)
		cmd = "echo \"%s;\" | %s %s%s " % sqlt
	
	select_map = {}
	select_list = commands.getoutput(cmd).split("\n")
	for row in select_list:
		cols = row.split('|')
		key = nullkey
		if( cols[keycol] ):
			key = cols[keycol]
		if( not key in select_map ):
			select_map[key] = []
		select_map[key] = cols[savecol]
	print select_map.keys()
	JoLog.setup("jo.sql").info(cmd)
	return select_map
	
def main():
	try:
		args1 = "hltmc:s:u:"
		args2 = ["help","select=","user=","list","table","map","sum="]
		opts, args = getopt.getopt(sys.argv[1:], args1,args2)
	except getopt.GetoptError:
		usage()
		sys.exit(2)
	select = ""
	list   = 0
	table  = 0
	map    = 0
	sumcols= None
	user   = None
	for o,a in opts:
		if o in ("-h", "--help"):
			usage()
			sys.exit()
		if o in ("-s", "--select"):
			select = a
		if o in ("-u", "--user"):
			user = a
		if o in ("-l", "--list"):
			list = 1
		if o in ("-t", "--table"):
			table = 1
		if o in ("-m", "--map"):
			map = 1
		if o in ("-c", "--sum"):
			sumcols = []
			for col in a.split(","):
				sumcols.append(int(col))
	# for in opts
	if( select ):
		if( list ):
			print "\n".join(sql_list(select,user))
		elif( table ):
			for row in sql_table(select,user,sumcols):
				print ", ".join(row)
		elif( map ):
			for (a, b) in sql_map(select,0,3,0,user).items():
				print(a,b)
		else:
			print sql_string(select,user)
	else:
		usage();

		
if __name__ == "__main__":
    main()
